package br.edu.ufcg.sig.server.persistence.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.postgis.Geometry;
import org.postgis.PGgeometry;
import org.postgis.Point;

import br.edu.ufcg.sig.server.beans.Delict;
import br.edu.ufcg.sig.server.exceptions.DelictNotFoundException;
import br.edu.ufcg.sig.server.persistence.jdbc.ConnectionFactory;
import br.edu.ufcg.sig.server.persistence.jdbc.Util;

/**
 * 
 * @author Diego Rodrigues
 * @author Odilon Lima
 *
 */
public class DelictJdbcDAO implements DelictDAO{
	
	private Connection dbConn;
	
	public DelictJdbcDAO(){
		try {
			dbConn = ConnectionFactory.newConnection();
		} catch (SQLException e) {			
			e.printStackTrace();
		}
	}
	
	/** {@inheritDoc} */	
	public int countCrimesByType(Geometry area, String type) {
		try {
            String sql = Util.DELICTS_BY_AREA_TYPE; 
           
            PreparedStatement s = dbConn.prepareStatement(sql);
            String verticesSTR = "";
    		for (int i = 0; i < area.numPoints(); i++) {
    			verticesSTR += area.getPoint(i).x + " " + area.getPoint(i).y + ", ";
    		}
    		verticesSTR = verticesSTR.trim();
    		verticesSTR.substring(0, verticesSTR.length() - 1);
            
            String polygon = "POLYGON(" + verticesSTR + ")";
            s.setString(1, polygon);
            s.setString(2, type);            
            ResultSet rs = s.executeQuery();
            s.close();  
            if(rs.next()){
            	return rs.getInt(1);
            } else {
            	throw new DelictNotFoundException("Erro ao recuperar quantidade de crimes por area.\n" +
            										"Polygon: " + polygon + "\n" + "Tipo: " + type);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (DelictNotFoundException de){
        	de.printStackTrace();
        }
		return 0;
		
	}
	
	public List<Delict> getDelictsByDate(String fromDate, String toDate) {
		List<Delict> delicts = new ArrayList<Delict>();
		try {
            String sql = Util.DELICTS_BY_DATE; 
           
            PreparedStatement s = dbConn.prepareStatement(sql);            
            s.setString(1, fromDate.toString());
            s.setString(2, toDate.toString());            
            ResultSet rs = s.executeQuery();
            s.close();  
            int index = 0;
            while(rs.next()){
            	index++;
            	Delict delict = new Delict();            
            	delict.setId((rs.getLong(1)));
            	delict.setDelictType(rs.getString(2));
            	delict.setLocationType(rs.getString(3));
            	delict.setDate(rs.getString(4));
            	delict.setDelictTime(rs.getString(5));
            	delict.setDescription(rs.getString(6));
            	delict.setUsedArmor(rs.getString(7));
            	delict.setQteVictims(rs.getInt(8));
            	delict.setQteRobbers(rs.getInt(9));
            	PGgeometry pg = (PGgeometry)(rs.getObject(10));
            	delict.setLocation(getPointByPGgeometry(pg));
            	delicts.add(delict);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } 	
		return delicts;
	}
	
	private Point getPointByPGgeometry(PGgeometry pg) throws SQLException{		
    	String aux[] = pg.toString().split(" ");
    	String coordinates = aux[0].substring(6) + " " + aux[1].substring(0, aux[1].length() - 1);
    	//creates a point with the string:
    	//
    	Point p = new Point(coordinates); 
    	return p;
	}

	/** {@inheritDoc} */
	public List<Delict> getAllDelicts() {
		List<Delict> delicts = new ArrayList<Delict>();
		try {
            String sql = Util.ALL_DELICTS; 
           
            PreparedStatement s = dbConn.prepareStatement(sql);            
            ResultSet rs = s.executeQuery();
            while(rs.next()){
            	Delict delict = new Delict();            
            	delict.setId((rs.getLong(1)));
            	delict.setDelictType(rs.getString(2));
            	delict.setLocationType(rs.getString(3));
            	delict.setDate(rs.getString(4));
            	delict.setDelictTime(rs.getString(5));
            	delict.setDescription(rs.getString(6));
            	delict.setUsedArmor(rs.getString(7));
            	delict.setQteVictims(rs.getInt(8));
            	delict.setQteRobbers(rs.getInt(9));
            	PGgeometry pg = (PGgeometry)(rs.getObject(10));            	
            	delict.setLocation(getPointByPGgeometry(pg));
            	delicts.add(delict);
            }
            s.close();           
        } catch (SQLException e) {
            e.printStackTrace();
        } 	
		return delicts;
	}
	
	/** {@inheritDoc} */
	public Delict getDelict(long id) throws DelictNotFoundException {
		Delict delict = null;
		try {
            String sql = Util.DELICT_BY_ID; 
           
            PreparedStatement s = dbConn.prepareStatement(sql);     
            s.setLong(1,id);
            ResultSet rs = s.executeQuery();
            while(rs.next()){
            	delict = new Delict();            
            	delict.setId((rs.getLong(1)));
            	delict.setDelictType(rs.getString(2));
            	delict.setLocationType(rs.getString(3));
            	delict.setDate(rs.getString(4));
            	delict.setDelictTime(rs.getString(5));
            	delict.setDescription(rs.getString(6));
            	delict.setUsedArmor(rs.getString(7));
            	delict.setQteVictims(rs.getInt(8));
            	delict.setQteRobbers(rs.getInt(9));
            	PGgeometry pg = (PGgeometry)(rs.getObject(10));
            	delict.setLocation(getPointByPGgeometry(pg));
            	
            }
            s.close();
        } catch (SQLException e) {
            e.printStackTrace();
        } 	
		return delict;
	}

	/** {@inheritDoc} */
	public List<String> getHotZones() {
		List<String> hotzones = new ArrayList<String>();
		try {
            String sql = Util.HOT_ZONES; 
           
            PreparedStatement s = dbConn.prepareStatement(sql);            
            ResultSet rs = s.executeQuery();
            while(rs.next()){
            	hotzones.add(rs.getString(1));
            }
            s.close();
        } catch (SQLException e) {
            e.printStackTrace();
        } 	
		return hotzones;
	}

	/** {@inheritDoc} */
	public void removeAllDelicts() {
		// TODO Auto-generated method stub
		
	}

	/** {@inheritDoc} */
	public void removeDelict(Delict delict) throws DelictNotFoundException {
		// TODO Auto-generated method stub
		
	}
	
	/** {@inheritDoc} */
	public void saveDelict(Delict delict) {
		try {
            String sql = Util.SAVE_DELICT; 
           
            PreparedStatement s = dbConn.prepareStatement(sql);
            s.setString(1, delict.getDelictType());
            s.setString(2, delict.getLocationType());                        
            s.setString(3, delict.getDate());
            s.setString(4, delict.getDelictTime());
            s.setString(5, delict.getDescription());
            s.setString(6, delict.getUsedArmor());
            s.setInt(7, delict.getQteVictims());
            s.setInt(8, delict.getQteRobbers());
            String location = "POINT(";
            location += delict.getLocation().x + " " + delict.getLocation().y;
            location += ")";
            s.setString(9, location);            
            s.execute();
            s.close();            
        } catch (SQLException e) {
            e.printStackTrace();
        }
		
	}

	/** {@inheritDoc} */
	public void updateDelict(Delict delict) throws DelictNotFoundException {
		// TODO Auto-generated method stub
		
	}

}
